package village.rainbow.dailyscene.database.dao;

import village.rainbow.dailyscene.database.DaoBase;
import village.rainbow.dailyscene.database.SqlSession;
import village.rainbow.dailyscene.datamodel.DataRow;
import village.rainbow.dailyscene.datamodel.DataTable;
import android.content.Context;

public class NormalDao extends DaoBase{

	private final static String _xmlFileName = "normal.xml";
	private Context _context;

	/**
	 * 로그인 체크 및 기타
	 * @param context
	 */
	public NormalDao(Context context) {
		super(context, _xmlFileName);
		_context = context;
	}
	
	public DataTable getDailyScene(String keyword) throws Exception{
		return getDailyScene(keyword, "");
	}
	
	public DataTable getDailyScene(String keyword, String date) throws Exception{
		DataTable result = new DataTable();
		DataRow bindParams = new DataRow();
		bindParams.put("KEY_WORD", keyword);
		
		String dateQueryStr = date.equals("") ? "" : "AND substr(A.SCENE_DATE, 0, 9) = \'" + date + "\'";
		bindParams.put("DATE", dateQueryStr);
		
		SqlSession session = SqlSession.openSession(_context, _xmlFileName);
		try{
			if(keyword.equals(""))
				result = session.selectList("SELECT_DAILY_SCENE", bindParams);
			else{
				result = session.selectList("SELECT_DAILY_SCENE_WITH_TEXT", bindParams);
			}
		}finally{
			session.close();
		}
		return result;
	}
	
	public int deleteScene(String sceneId) throws Exception {
		int result = 0;
		
		DataRow bindParams = new DataRow();
		bindParams.put("SCENE_ID", sceneId);
		SqlSession session = SqlSession.openSession(_context, _xmlFileName);
		try{
			result = session.delete("DELETE_SCENE_HEADER", bindParams);
			if(result != 1)
				throw new Exception("Header Delete failed");
			
			result = session.delete("DELETE_SCENE_DETAIL", bindParams);
			if(result != 1)
				throw new Exception("Detail Delete failed");
			
			session.commit();
		}finally{
			session.close();
		}
		return result;
	}
	
	public String insertScene(DataRow headerData, DataTable detailData) throws Exception {
		String sceneId = "";
		int result = 0;
		SqlSession session = SqlSession.openSession(_context, _xmlFileName);
		
		try{
			// 헤더에 데이터를 넣으면서 동시에 SCENE_ID를 채번한다.
			result = session.insert("INSERT_SCENE_HEADER", headerData);
			if(result != 1)
				throw new Exception("Header insert failed");
			
			DataRow headerRow = session.selectOne("SELECT_SCENE_ID", headerData);
			
			sceneId =  headerRow.getString("SCENE_ID", "");
			
			for(DataRow detail : detailData){
				detail.put("SCENE_ID", sceneId);
				result = session.insert("INSERT_SCENE_DETAIL", detail);
				if(result != 1)
					throw new Exception("detail insert failed");
			}
			session.commit();
			
			
		}finally{
			session.close();
		}
		return sceneId;
	}

	public int updateScene(DataRow headerData, DataTable detailData) throws Exception {
		int result = 0;
		SqlSession session = SqlSession.openSession(_context, _xmlFileName);
		
		try{
			result = session.update("UPDATE_SCENE_HEADER", headerData);
			if(result != 1)
				throw new Exception("Header update failed");
			
			String sceneId = headerData.getString("SCENE_ID", "");
			
			for(DataRow detail : detailData){
				
				if(detail.getString("SCENE_DETAIL_ID", "").equals("")){
					detail.put("SCENE_ID", sceneId);
					result = session.insert("INSERT_SCENE_DETAIL", detail);
					if(result != 1)
						throw new Exception("detail insert failed");
				}else{
					result = session.update("UPDATE_SCENE_DETAIL", detail);
					if(result != 1)
						throw new Exception("detail update failed");	
				}
			}
			session.commit();
		}finally{
			session.close();
		}
		return result;
	}
	
	public DataRow getScene(String sceneId) throws Exception{
		DataRow result = new DataRow();

		DataRow bindParams = new DataRow();
		bindParams.put("SCENE_ID", sceneId);
		SqlSession session = SqlSession.openSession(_context, _xmlFileName);
		try{
			result = session.selectOne("SELECT_SCENE_HEADER", bindParams);
			DataTable detail = session.selectList("SELECT_SCENE_DETAIL", bindParams);
			result.put("SCENE_DETAIL", detail);
			
		}finally{
			session.close();
		}
		return result;
	}
	
}
